#! /usr/bin/env python  
# -*- coding: utf-8 -*-  
# __author__ = "shuke"  
# Date: 2018/5/13  
import hashlib   
import pymysql  
from dbutils.pooled_db import PooledDB
import sys,os
sys.path.append(os.getcwd())
from src.utils.tool import Tool

#pwd = '123'
#result = Tool.md5_32(pwd)
#print(result)

# 创建数据库连接池
tempPool = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接
    maxcached=5,  # 链接池中最多闲置的链接
    maxshared=3,  # 链接池中最多共享的链接数量
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待
    host='192.168.10.191',
    port=3306,
    user='writer',
    password='zlc1002020',
    database='wisdom-exam-db',
    charset='utf8mb4'  # 设置编码
)

examPool = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接
    maxcached=5,  # 链接池中最多闲置的链接
    maxshared=3,  # 链接池中最多共享的链接数量
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待
    host='192.168.10.191',
    port=3306,
    user='writer',
    password='zlc1002020',
    database='wisdom-exam-db',
    charset='utf8mb4'  # 设置编码
)

# 获取数据
def getScriptQuestions():
    conn = tempPool.connection()
    cursor = conn.cursor()

    # 执行SQL语句
    sql = "SELECT id,topic_key,subject_id,section_id,stem,answer,analysis,choicesList,year,knowledge_ids,subQuestions FROM script_questions WHERE topic_id = 0 AND is_sys = 0 AND has_sub = 0 AND is_new = 0 limit 100"
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()
    
    return result

# 获取试题
def getTopicsInfo(topicKey):
    conn = examPool.connection()
    cursor = conn.cursor()

    topic_key_hash = Tool.md5_32(str(topicKey))

    # 执行SQL语句
    sql = "SELECT id FROM topics_info WHERE topic_key_hash='"+topic_key_hash+"'"
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()
    
    return result

# 获取试题
def getTopicsInfoOne(topicKey):
    conn = examPool.connection()
    cursor = conn.cursor()

    topic_key_hash = Tool.md5_32(topicKey)

    # 执行SQL语句
    sql = "SELECT id FROM topics_info WHERE topic_key_hash='"+topic_key_hash+"'"
    cursor.execute(sql)

    # 获取查询结果:一条数据
    result = cursor.fetchone()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()
    
    return result

# 获取试题详情
def getTopicsDetail(topicKey):
    conn = examPool.connection()
    cursor = conn.cursor()

    topic_key_hash = Tool.md5_32(topicKey)

    # 执行SQL语句
    sql = "SELECT id FROM topics_detail WHERE topic_key_hash='"+topic_key_hash+"'"
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()
    
    return result

# 保存试题
def saveTopicsInfo(row):

    # 插入数据初始化 id,topic_key,subject_id,section_id,stem,answer,analysis,choicesList,year,knowledge_ids,subQuestions
    topic_key = str(row[1])
    subject_id = str(row[2])
    learn_phase = '3'
    section_id = str(row[2])
    knowledges = str(row[9])
    year = str(row[8])
    source = 2                                  # 1:菁优网题, 2：深度学伴系统
    topic_key_hash = Tool.md5_32(topic_key)

    # 执行SQL语句
    hasResult = getTopicsInfo(row[1])
    hasLength = len(hasResult)
    conn = examPool.connection()
    cursor = conn.cursor()

    if hasLength == 0:
      # 执行插入数据操作
      sql = "INSERT INTO topics_info (topic_key,subject_id,learn_phase,section_id,knowledges,year,source,topic_key_hash) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
      values = (topic_key, subject_id, learn_phase, section_id, knowledges, year, source,topic_key_hash)
      cursor.execute(sql, values)
      # 提交事务
      conn.commit()

    else:  
      #更新单行数据
      sql = "UPDATE topics_info SET topic_key = %s,subject_id = %s,learn_phase =%s,section_id =%s, knowledges =%s, year =%s, source =%s, topic_key_hash =%s WHERE topic_key_hash = %s"
      cursor.execute(sql, (topic_key, subject_id, learn_phase, section_id, knowledges, year, source,topic_key_hash,topic_key_hash))
      # 提交事务
      conn.commit()

    # 查询插入的数据
    hasResult = getTopicsInfo(row[1])    
    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()   

    return hasResult

# 保存试题详情      
def saveTopicsDetail(row,topicId):

    # 插入数据初始化 id,topic_key,subject_id,section_id,stem,answer,analysis,choicesList,year,knowledge_ids,subQuestions
    topic_key = str(row[1])             # $row['topic_key']
    content = str(row[4])               # $row['stem'],
    answer = str(row[5])                # $row['answer'],
    display_answer = str(row[5])        # $row['answer'],
    analyse = str(row[6])               # $row['analysis']
    options = str(row[7])               # $row['choicesList']
    source = 2                          # 数据来源
    topic_key_hash = Tool.md5_32(topic_key)  # 索引字段

     # 执行SQL语句
    hasResult = getTopicsDetail(row[1])
    hasLength = len(hasResult)
    conn = examPool.connection()
    cursor = conn.cursor()

    if hasLength == 0:
      # 执行插入数据操作
      sql = "INSERT INTO topics_detail (topic_id,topic_key,content,answer,display_answer,analyse,options,source,topic_key_hash) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
      values = (topicId,topic_key, content, answer, display_answer, analyse, options, source,topic_key_hash)
      cursor.execute(sql, values)
      # 提交事务
      conn.commit()

    else:
      #更新单行数据
      sql = "UPDATE topics_detail SET topic_id = %s,topic_key = %s,content =%s,answer =%s, display_answer =%s, analyse =%s, options =%s, source =%s,topic_key_hash =%s  WHERE topic_key_hash = %s"
      cursor.execute(sql, (topicId,topic_key, content, answer, display_answer, analyse, options, source,topic_key_hash,topic_key_hash))
      # 提交事务
      conn.commit()

    # 查询插入的数据
    hasResult = getTopicsDetail(row[1])
    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()

    return hasResult

def updateScriptQuestions(id,topicId):
    conn = tempPool.connection()
    cursor = conn.cursor()

    # 执行SQL语句
    #更新单行数据
    sql = "UPDATE script_questions SET topic_id = %s,is_sys = 1 WHERE id = %s"
    cursor.execute(sql, (topicId,id))

    #提交事务
    conn.commit()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()

def run():
  i = 1
  while i <= 50000:
    # 临时列表
    tempSectionList = getScriptQuestions()

    for temprow in tempSectionList:

        if temprow[1] == None:
           continue
        # 获取一条数据
        #result = getTopicsInfoOne(temprow[1])
        #print(result[0])
        topicIds = saveTopicsInfo(temprow)
        length = len(topicIds)
        if length >= 1:

            for topicData in topicIds:
                topicId = str(topicData[0])
                saveTopicsDetail(temprow,topicId)

                updateScriptQuestions(temprow[0],topicId)
                print("第"+str(i)+" 页 写入试题：原来ID["+str(temprow[0])+ "] ---题库ID:-- "+topicId)    
    
    i = i+1    # 更新 counter

run()    
